//Author : Bui Huu Loc
//Date : 18-05-2009
using System;
using System.Xml;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Text;
using System.Drawing;
using LibKetoan;
using System.Windows.Forms;

namespace Ketoan
{
	
	public class frmReport_dn1_class
	{
		#region Constructor
		public frmReport_dn1_class()
		{
			
		}
		#endregion

		#region Variables
		bool flag_dsnull;		
		DataSet ds_Data,ds_Data2;
		string sql ;
		#endregion

		#region Destructor
		public void Dispose()
		{
			try
			{
				ds_Data.Dispose();
			}
			catch{};
		}
		#endregion

		#region Functions
		/// <summary>
		/// 18-05-2009
		/// </summary>
		public DataSet get_SoNhatKyThuTien(AccessData a, string stk, string mm1,string mm2, string yy)
		{
			sql = "";
			sql = "select distinct ct.soctg,to_char(ct.ngaygs,'dd/mm/yy') as ngaygs,to_char(ct.ngayg,'dd/mm/yy') as ngayg,ct.thanggs,ctct.noidung as noidung, ";
			sql += " case when tkcoa.stk like '511%' then ctct.stien else 0 end as tien511,case when tkcoa.stk like '131%' then ctct.stien else 0 end as tien131,";
			sql += " case when not(tkcoa.stk like '511%' or tkcoa.stk like '131%') then tkcoa.stk else '' end as sohieutk,";
			sql += " case when not(tkcoa.stk like '511%' or tkcoa.stk like '131%') then ctct.stien else 0 end as tienkhac ";
			sql += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tkcoa ";
			sql += " on tkcoa.id = ctct.tkcoa inner join " + a.user + ".a_dmtk tknoa on tknoa.id = ctct.tknoa";
			sql += " where ctct.tonghop=1 and tknoa.stk like '"+ stk + "%' ";
			sql += " and ct.thanggs between '" + mm1 + "' and '" + mm1 + "'";
			sql += " order by ct.thanggs";
			try
			{
				ds_Data = a.get_data(sql).Copy();
				if(ds_Data.Tables[0].Rows.Count<=0)
					flag_dsnull=true;
			}
			catch
			{
				flag_dsnull = true;
			}  
			//			if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
			//			ds_Data.WriteXml("..\\xml\\KT_SoNhatKyThuTien.xml",XmlWriteMode.WriteSchema);
			if (flag_dsnull) return null;
			else return ds_Data;
		}

		
		/// <summary>
		/// 19-05-2009
		/// </summary>
		public DataSet get_SoNhatKyChiTien(AccessData a, string stk, string mm1,string mm2, string yy)
		{
			sql = "";
			sql = "select ct.soctg,to_char(ct.ngaygs,'dd/mm/yy') as ngaygs,to_char(ct.ngayg,'dd/mm/yy') as ngayg,ctct.noidung as noidung,  ";
			sql += " case when tknoa.stk like '331%' then ctct.stien else 0 end as tien331,";
			sql += " case when tknoa.stk like '133%' then ctct.stien else 0 end as tien133,";
			sql += " case when not(tknoa.stk like '331%' or tknoa.stk like '133%') then tknoa.stk else '' end as sohieutk,";
			sql += " case when not(tknoa.stk like '331%' or tknoa.stk like '133%') then ctct.stien else 0 end as tienkhac ";
			sql += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tkcoa ";
			sql += " on tkcoa.id = ctct.tkcoa inner join " + a.user + ".a_dmtk tknoa on tknoa.id = ctct.tknoa";
			sql += " where ctct.tonghop=1 and tkcoa.stk like '"+ stk + "%' ";
			sql += " and ct.thanggs between '" + mm1 + "' and '" + mm1 + "'";
			sql += " order by ct.thanggs";
			try
			{
				ds_Data = a.get_data(sql).Copy();
				if(ds_Data.Tables[0].Rows.Count<=0)
					flag_dsnull=true;
			}
			catch
			{
				flag_dsnull = true;
			}  
			//			if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
			//			ds_Data.WriteXml("..\\xml\\KT_SoNhatKyChiTien.xml",XmlWriteMode.WriteSchema);
			if (flag_dsnull) return null;
			else return ds_Data;
		}


		/// <summary>
		/// 20-05-2009
		/// </summary>
		public DataSet get_SoNhatKyMuaHang(AccessData a, string stk, string mm1,string mm2, string yy)
		{			
			sql = "";
			sql = "select ct.soctg,to_char(ct.ngaygs,'dd/mm/yy') as ngaygs,to_char(ct.ngayg,'dd/mm/yy') as ngayg,ctct.noidung as noidung,  ";
			sql += " case when tknoa.stk like '156%' then ctct.stien else 0 end as tienhh,";
			sql += " case when tknoa.stk like '152%' then ctct.stien else 0 end as tienvt,";
			sql += " case when not(tknoa.stk like '156%' or tknoa.stk like '152%') then tknoa.stk else '' end as sohieutk,";
			sql += " case when not(tknoa.stk like '156%' or tknoa.stk like '152%') then ctct.stien else 0 end as tienkhac ";
			sql += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tkcoa ";
			sql += " on tkcoa.id = ctct.tkcoa inner join " + a.user + ".a_dmtk tknoa on tknoa.id = ctct.tknoa";
			sql += " where ctct.tonghop=1 and tkcoa.stk like '"+ stk + "%' ";
			sql += " and ct.thanggs between '" + mm1 + "' and '" + mm1 + "'";
			sql += " order by ct.thanggs";
			try
			{
				ds_Data = a.get_data(sql).Copy();
				if(ds_Data.Tables[0].Rows.Count<=0)
					flag_dsnull=true;
			}
			catch
			{
				flag_dsnull = true;
			}  
			//			if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
			//			ds_Data.WriteXml("..\\xml\\KT_SoNhatKyMuaHang.xml",XmlWriteMode.WriteSchema);
			if (flag_dsnull) return null;
			else return ds_Data;
		}


		/// <summary>
		/// 20-05-2009
		/// </summary>
		public DataSet get_SoNhatKyBanHang(AccessData a, string stk, string mm1,string mm2, string yy)
		{
			sql = "";
			sql = "select ct.soctg,to_char(ct.ngaygs,'dd/mm/yy') as ngaygs,to_char(ct.ngayg,'dd/mm/yy') as ngayg,ctct.noidung as noidung, ";
			sql += " case when tkcoa.stk like '5111%' then ctct.stien else 0 end as tien_hh,";
			sql += " case when tkcoa.stk like '5113%' then ctct.stien else 0 end as tien_tp,";
			sql += " case when tkcoa.stk like '3331%' then ctct.stien else 0 end as tien_gtgt,";
			sql += " case when not(tkcoa.stk like '5111%' or tkcoa.stk like '5113%' or tkcoa.stk like '3331%') then tkcoa.stk else '' end as sohieutk,";
			sql += " case when not(tkcoa.stk like '5111%' or tkcoa.stk like '5113%' or tkcoa.stk like '3331%') then ctct.stien else 0 end as tienkhac ";
			sql += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tkcoa ";
			sql += " on tkcoa.id = ctct.tkcoa inner join " + a.user + ".a_dmtk tknoa on tknoa.id = ctct.tknoa";
			sql += " where ctct.tonghop=1 and tknoa.stk like '"+ stk + "%' ";
			sql += " and ct.thanggs between '" + mm1 + "' and '" + mm1 + "'";
			sql += " order by ct.thanggs";
			try
			{
				ds_Data = a.get_data(sql).Copy();
				if(ds_Data.Tables[0].Rows.Count<=0)
					flag_dsnull=true;
			}
			catch
			{
				flag_dsnull = true;
			}  
			if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
			ds_Data.WriteXml("..\\xml\\KT_SoNhatKyBanHang.xml",XmlWriteMode.WriteSchema);
			if (flag_dsnull) return null;
			else return ds_Data;
		}


		/// <summary>
		/// 21-05-2009
		/// </summary>
		public DataSet get_SoChiTietS31(AccessData a, string mm1,string mm2, string yy,string stk,string idLoaidt,string idDT,string idKM,int i_tinhchat)
		{			
			#region tinh so du dau thang
			int tuthang = Convert.ToInt32(mm1);
			int denthang = Convert.ToInt32(mm2);
			string sql_dk = "";
			for(int i = tuthang; i<= denthang ;i++ )
			{
				sql_dk += " (select sum(t.sdno) as sodudk_no, sum(t.sdco) as sodudk_co ,t.thang from (";
				sql_dk += " select sum(sdco" + i.ToString().PadLeft(2,'0') + ") as sdco,sum(sdno" + i.ToString().PadLeft(2,'0') + ") as sdno,";
				sql_dk += "'" + i.ToString().PadLeft(2,'0') + "' as thang,tk.stk ";
				sql_dk += " from " + a.user + ".a_sd" + yy + " sd left join " + a.user + ".a_dmtk tk on sd.stka = tk.id ";			
				sql_dk += " left join " + a.user + ".a_dmdt dt on sd.madt =  dt.id " ;
				sql_dk += " left join " + a.user + ".a_dmloaidt loaidt on loaidt.id = dt.loaidt ";
				sql_dk += " where tk.stk like '" + stk + "%'";
				sql_dk += (idLoaidt != "" ? " and loaidt.id= " + idLoaidt : "");
				sql_dk += (idDT != "" ? " and sd.madt= " + idDT : "");
				sql_dk += (idKM != "" ? " and sd.khoanmuc= " + idKM : "");
				sql_dk += " group by tk.stk ) t group by t.thang ";
				if(i<denthang)
					sql_dk+=") union ";
				else
					sql_dk+=")";
			}	
			try
			{
				ds_Data2 = a.get_data(sql_dk);
			}
			catch 
			{
				flag_dsnull=true;
			}			
			#endregion			
			
			#region FSNO,FSCO
			string sql_fs = "";
			sql_fs = "select ct.soctg,to_char(ct.ngaygs,'dd/mm/yy') as ngaygs,to_char(ct.ngayg,'dd/mm/yy') as ngayg,ctct.noidung as noidung, ";
			sql_fs += " tknoa.stk as tkno, tkcoa.stk as tkco,ct.thanggs as thang, ";
			sql_fs += " case when tknoa.stk like '" + stk + "%' then tkcoa.stk else tknoa.stk end as tkdoiung,";
			sql_fs += " case when tknoa.stk like '" + stk + "%' then ctct.stien else 0 end as fsno, ";
			sql_fs += " case when tknoa.stk not like '" + stk + "%' then ctct.stien else 0 end as fsco, ";
			sql_fs += " 0 luykenam_co, 0 luykenam_no, 0 luykequy_co, 0 luykequy_no,0 sodu_no,0 sodu_co,0 as sapxep ";
			sql_fs += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tkcoa ";
			sql_fs += " on tkcoa.id = ctct.tkcoa inner join " + a.user + ".a_dmtk tknoa on tknoa.id = ctct.tknoa";
			sql_fs += " where ((tknoa.stk like '"+ stk	+ "%' " + (idKM != "" ? " and ctct.kmno= " + idKM + ")" : ")") + " or (tkcoa.stk like '"+ stk+ "%'" + (idKM != "" ? " and ctct.kmco= " + idKM + "))" : "))");
			sql_fs += (idLoaidt != "" ? " and ct.loaidt= " + idLoaidt : "");
			sql_fs += (idDT != "" ? " and ct.madt= " + idDT : "");
			sql_fs += " and ctct.tonghop= 1 ";
			sql_fs += " and ct.thanggs between '" + mm1 + "' and '" + mm2 + "'" ;
			sql_fs += " order by ct.ngaygs,ct.ngayg,ct.soctg";
			try
			{
				ds_Data = a.get_data(sql_fs);
				if(ds_Data!=null)
					ds_Data.Merge(ds_Data2);
				ds_Data.AcceptChanges();
			}
			catch 
			{
				flag_dsnull=true;
			}
			#endregion
									
			#region LKFS dau quy, dau nam
			if(flag_dsnull!=true)
			{
				for(int lk=tuthang;lk<=denthang;lk++)
				{
					DataRow dr_LuyKe = ds_Data.Tables[0].NewRow();
					dr_LuyKe["soctg"]="";
					dr_LuyKe["ngaygs"]="";
					dr_LuyKe["ngayg"]="";
					dr_LuyKe["noidung"]="";
					dr_LuyKe["tkno"]="";
					dr_LuyKe["tkco"]="";
					dr_LuyKe["thang"]=lk.ToString().PadLeft(2,'0');				
					dr_LuyKe["tkdoiung"]="";
					dr_LuyKe["fsno"]= 0;
					dr_LuyKe["fsco"]= 0;

					int CT_Quy = ((lk-1)/3)*3+1;
					if(lk==tuthang)
					{
						dr_LuyKe["luykenam_co"]=get_lkfsNamQuy(a,yy,1,tuthang,stk,idLoaidt,idDT,idKM,2);
						dr_LuyKe["luykenam_no"]=get_lkfsNamQuy(a,yy,1,tuthang,stk,idLoaidt,idDT,idKM,1);
						dr_LuyKe["luykequy_co"]=get_lkfsNamQuy(a,yy,CT_Quy,tuthang,stk,idLoaidt,idDT,idKM,2);
						dr_LuyKe["luykequy_no"]=get_lkfsNamQuy(a,yy,CT_Quy,tuthang,stk,idLoaidt,idDT,idKM,1);
						
					}
					else
					{
						dr_LuyKe["luykenam_co"]=get_lkfsNamQuy(a,yy,1,lk,stk,idLoaidt,idDT,idKM,2);
						dr_LuyKe["luykenam_no"]=get_lkfsNamQuy(a,yy,1,lk,stk,idLoaidt,idDT,idKM,1);
						dr_LuyKe["luykequy_co"]=get_lkfsNamQuy(a,yy,CT_Quy,lk,stk,idLoaidt,idDT,idKM,2);
						dr_LuyKe["luykequy_no"]=get_lkfsNamQuy(a,yy,CT_Quy,lk,stk,idLoaidt,idDT,idKM,1);
						
					}
					ds_Data.Tables[0].Rows.Add(dr_LuyKe);
					ds_Data.AcceptChanges();
				}
			}
			#endregion

			ds_Data.WriteXml("..\\xml\\KT_SoChiTietS31.xml",XmlWriteMode.WriteSchema);
			if (flag_dsnull) return null;
			else return ds_Data;			
		}

		
		/// <summary>
		/// tinh lkfs no/co dau quy/nam
		/// </summary>		
		public decimal get_lkfsNamQuy(AccessData a, string s_yy,int CT_Quy,int i_tu,string stk,string idLoaidt,string idDT,string idKM,int i_tinhchat)
		{
			sql = "";
			decimal d_FSluyke=0;
			for (int i=CT_Quy;i<i_tu;i++)
			{
				string s_Thang_i=i.ToString().PadLeft(2,'0');				
				if (i_tinhchat==1) sql=  "select sum(sd.fsno"+s_Thang_i+") fsluyke";
				else sql=  "select sum(sd.fsco"+s_Thang_i+") fsluyke";
				sql += " from " + a.user + ".a_sd" + s_yy + " sd left join " + a.user + ".a_dmtk tk on sd.stka = tk.id ";			
				sql += " left join " + a.user + ".a_dmdt dt on sd.madt =  dt.id " ;
				sql += " left join " + a.user + ".a_dmloaidt loaidt on loaidt.id = dt.loaidt ";
				sql += " where tk.stk like '" + stk + "%'";
				sql += (idLoaidt != "" ? " and loaidt.id= " + idLoaidt : "");
				sql += (idDT != "" ? " and sd.madt= " + idDT : "");
				sql += (idKM != "" ? " and sd.khoanmuc= " + idKM : "");				
				try
				{
					d_FSluyke+=decimal.Parse(a.get_data(sql).Tables[0].Rows[0][0].ToString());
				}
				catch{};
				
			}
			return d_FSluyke;
		}


		/// <summary>
		/// 24-05-2009
		/// </summary>
		public DataSet get_SoChiTietBanHang(AccessData a, string stk, string mm1,string mm2, string yy)//,string idLoaidt,string idDT,string idKM)
		{
			#region tinh so du dau thang			
			string sql_dk = "";
			sql_dk = "select sum(sdco" + mm1 + ") as sdco_dk ";
			sql_dk += " from " + a.user + ".a_sd" + yy + " sd left join " + a.user + ".a_dmtk tk on sd.stka = tk.id ";			
			sql_dk += " left join " + a.user + ".a_dmdt dt on sd.madt =  dt.id " ;
			sql_dk += " left join " + a.user + ".a_dmloaidt loaidt on loaidt.id = dt.loaidt ";
			sql_dk += " where tk.stk like '" + stk + "%'";			
			try
			{
				ds_Data2 = a.get_data(sql_dk);
			}
			catch 
			{
				flag_dsnull=true;
			}			
			#endregion			

			sql = "";
			sql = "select ct.soctg,to_char(ct.ngaygs,'dd/mm/yy') as ngaygs,to_char(ct.ngayg,'dd/mm/yy') as ngayg,ctct.noidung as noidung,0 as sapxep, ";
			sql += " case when tknoa.stk like '" + stk + "%' then tkcoa.stk else tknoa.stk end as tkdoiung,";
			sql += " case when tkcoa.stk like '" + stk + "%' then ctct.stien else 0 end as co,";
			sql += " case when (tkcoa.stk like '521%' and tknoa.stk like '" + stk + "%') then ctct.stien else 0 end as tien521,";
			sql += " case when (tkcoa.stk like '531%' and tknoa.stk like '" + stk + "%') then ctct.stien else 0 end as tien531,";
			sql += " case when (tkcoa.stk like '532%' and tknoa.stk like '" + stk + "%') then ctct.stien else 0 end as tien532 ";
			sql += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tkcoa ";
			sql += " on tkcoa.id = ctct.tkcoa inner join " + a.user + ".a_dmtk tknoa on tknoa.id = ctct.tknoa";
			sql += " where ctct.tonghop=1 and (tkcoa.stk like '"+ stk + "%' ";
			sql += " or (tknoa.stk like '" + stk + "%' and ctct.tkcoa in (675,227,676)))";
			sql += " and ct.thanggs between '" + mm1 + "' and '" + mm2 + "'";
			sql += " order by ct.thanggs";
			
			try
			{
				ds_Data = a.get_data(sql).Copy();
				if(ds_Data.Tables[0].Rows.Count<=0)
					flag_dsnull=true;				
				ds_Data.Merge(ds_Data2);
				ds_Data.AcceptChanges();
			}
			catch
			{
				flag_dsnull = true;
			}  
			if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
			ds_Data.WriteXml("..\\xml\\KT_SoChiTietBanHang.xml",XmlWriteMode.WriteSchema);
			if (flag_dsnull) return null;
			else return ds_Data;
		}


		/// <summary>
		/// 26-05-2009
		/// </summary>		
		public DataSet get_SoChiPhiSXKD(AccessData a, string stk, string mm1,string mm2, string yy)
		{
			DataSet ds_tk = new DataSet();
			sql ="";
			sql = "select stk from a_dmtk  where stk like '" + stk + "%' order by stk";
			try
			{
				ds_tk = a.get_data(sql);
			}
			catch 
			{
				flag_dsnull=true;
			}			
			int count = ds_tk.Tables[0].Rows.Count;
			string stk1 ="",stk2 ="",stk3 ="",stk4 ="",stk5 ="",stk6 ="",stk7 ="",stk8 ="";
			switch (count)
			{
				case 1 : 
					stk1 = ds_tk.Tables[0].Rows[0][0].ToString();
					break;
				case 2 : 
					stk1 = ds_tk.Tables[0].Rows[1][0].ToString();
					break;
				case 3 : 
					stk1 = ds_tk.Tables[0].Rows[1][0].ToString();
					stk2 = ds_tk.Tables[0].Rows[2][0].ToString();
					break;
				case 4 : 
					stk1 = ds_tk.Tables[0].Rows[1][0].ToString();
					stk2 = ds_tk.Tables[0].Rows[2][0].ToString();
					stk3 = ds_tk.Tables[0].Rows[3][0].ToString();
					break;
				case 5 : 
					stk1 = ds_tk.Tables[0].Rows[1][0].ToString();
					stk2 = ds_tk.Tables[0].Rows[2][0].ToString();
					stk3 = ds_tk.Tables[0].Rows[3][0].ToString();
					stk4 = ds_tk.Tables[0].Rows[4][0].ToString();
					break;
				case 6 : 
					stk1 = ds_tk.Tables[0].Rows[1][0].ToString();
					stk2 = ds_tk.Tables[0].Rows[2][0].ToString();
					stk3 = ds_tk.Tables[0].Rows[3][0].ToString();
					stk4 = ds_tk.Tables[0].Rows[4][0].ToString();
					stk5 = ds_tk.Tables[0].Rows[5][0].ToString();
					break;
				case 7 : 
					stk1 = ds_tk.Tables[0].Rows[1][0].ToString();
					stk2 = ds_tk.Tables[0].Rows[2][0].ToString();
					stk3 = ds_tk.Tables[0].Rows[3][0].ToString();
					stk4 = ds_tk.Tables[0].Rows[4][0].ToString();
					stk5 = ds_tk.Tables[0].Rows[5][0].ToString();
					stk6 = ds_tk.Tables[0].Rows[6][0].ToString();
					break;
				case 8 : 
					stk1 = ds_tk.Tables[0].Rows[1][0].ToString();
					stk2 = ds_tk.Tables[0].Rows[2][0].ToString();
					stk3 = ds_tk.Tables[0].Rows[3][0].ToString();
					stk4 = ds_tk.Tables[0].Rows[4][0].ToString();
					stk5 = ds_tk.Tables[0].Rows[5][0].ToString();
					stk6 = ds_tk.Tables[0].Rows[6][0].ToString();
					stk7 = ds_tk.Tables[0].Rows[7][0].ToString();
					break;
				case 9 : 
					stk1 = ds_tk.Tables[0].Rows[1][0].ToString();
					stk2 = ds_tk.Tables[0].Rows[2][0].ToString();
					stk3 = ds_tk.Tables[0].Rows[3][0].ToString();
					stk4 = ds_tk.Tables[0].Rows[4][0].ToString();
					stk5 = ds_tk.Tables[0].Rows[5][0].ToString();
					stk6 = ds_tk.Tables[0].Rows[6][0].ToString();
					stk7 = ds_tk.Tables[0].Rows[7][0].ToString();
					stk8 = ds_tk.Tables[0].Rows[8][0].ToString();
					break;					
			}
			sql ="";
			sql = "select ct.soctg,to_char(ct.ngaygs,'dd/mm/yy') as ngaygs,to_char(ct.ngayg,'dd/mm/yy') as ngayg,ctct.noidung as noidung,";
			sql += " case when tknoa.stk like '" + stk + "%' then tkcoa.stk else tknoa.stk end as tkdoiung,";
			sql += " case when tknoa.stk like '" + stk + "%' then ctct.stien else 0 end as stien,tknoa.stk,";
			sql += (stk1 != "" ? " case when (tknoa.stk = '" + stk1 + "') then ctct.stien else 0 end as stk1," : "0 as stk1," ) ;
			sql += (stk2 != "" ? " case when (tknoa.stk = '" + stk2 + "') then ctct.stien else 0 end as stk2," : "0 as stk2," ) ;
			sql += (stk3 != "" ? " case when (tknoa.stk = '" + stk3 + "') then ctct.stien else 0 end as stk3," : "0 as stk3," ) ;
			sql += (stk4 != "" ? " case when (tknoa.stk = '" + stk4 + "') then ctct.stien else 0 end as stk4," : "0 as stk4," ) ;
			sql += (stk5 != "" ? " case when (tknoa.stk = '" + stk5 + "') then ctct.stien else 0 end as stk5," : "0 as stk5," ) ;
			sql += (stk6 != "" ? " case when (tknoa.stk = '" + stk6 + "') then ctct.stien else 0 end as stk6," : "0 as stk6," ) ;
			sql += (stk7 != "" ? " case when (tknoa.stk = '" + stk7 + "') then ctct.stien else 0 end as stk7," : "0 as stk7," ) ;
			sql += (stk8 != "" ? " case when (tknoa.stk = '" + stk8 + "') then ctct.stien else 0 end as stk8," : "0 as stk8," ) ;
			
			sql += (stk1 != "" ? "'" + stk1 + "' as tk1," : "'' as tk1," ) ;
			sql += (stk2 != "" ? "'" + stk2 + "' as tk2," : "'' as tk2," ) ;
			sql += (stk3 != "" ? "'" + stk3 + "' as tk3," : "'' as tk3," ) ;
			sql += (stk4 != "" ? "'" + stk4 + "' as tk4," : "'' as tk4," ) ;
			sql += (stk5 != "" ? "'" + stk5 + "' as tk5," : "'' as tk5," ) ;
			sql += (stk6 != "" ? "'" + stk6 + "' as tk6," : "'' as tk6," ) ;
			sql += (stk7 != "" ? "'" + stk7 + "' as tk7," : "'' as tk7," ) ;
			sql += (stk8 != "" ? "'" + stk8 + "' as tk8," : "'' as tk8," ) ;
			sql = cutString(sql);
			sql += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tkcoa ";
			sql += " on tkcoa.id = ctct.tkcoa inner join " + a.user + ".a_dmtk tknoa on tknoa.id = ctct.tknoa";
			sql += " where ctct.tonghop=1 and tknoa.stk like '"+ stk + "%' ";			
			sql += " and ct.thanggs between '" + mm1 + "' and '" + mm2 + "'";
			sql += " order by ct.thanggs,ct.ngayg,ngaygs,ct.soctg";

			try
			{
				ds_Data = a.get_data(sql);
				if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
				ds_Data.WriteXml("..\\xml\\KT_SoChiPhiSXKD.xml",XmlWriteMode.WriteSchema);
			}
			catch 
			{
				flag_dsnull=true;
			}		

			if (flag_dsnull) return null;
			else return ds_Data;
		
		}


		/// <summary>
		/// 29-05-2009
		/// </summary>
		public DataSet get_TongHopTaiKhoanTienGui(AccessData a, string stk, string mm1,string mm2, string yy)
		{
			int _tu = 0,_den=0, _tudaunam = 1;
			string fsno = "", fsco = "", lk_fsco = "", lk_fsno = "";
			_tu = Convert.ToInt16(mm1.Substring(0,2).ToString());
			_den = Convert.ToInt16(mm2.Substring(0,2).ToString());
			// tinh fsco,fsno
			while(_tu <= _den)
			{				
				fsno += "fsno" +  (_tu.ToString().PadLeft(2,'0')) + "+";
				fsco += "fsco" +  (_tu.ToString().PadLeft(2,'0')) + "+";				
				_tu++;
			}
			if (!fsco.Equals(""))
			{
				fsco = fsco.Substring(0,fsco.Length-1);
				fsno = fsno.Substring(0,fsno.Length-1);
				fsco = "(" + fsco + ")";
				fsno = "(" + fsno + ")";
			}
			// tinh lk tu dau nam
			while(_tudaunam <= _tu - 1)
			{
				lk_fsco += "fsco" +  (_tudaunam.ToString().PadLeft(2,'0')) + "+";
				lk_fsno += "fsno" +  (_tudaunam.ToString().PadLeft(2,'0')) + "+";				
			
				_tudaunam++;
			}
			if(!lk_fsco.Equals(""))
			{
				lk_fsco = lk_fsco.Substring(0,lk_fsco.Length-1);
				lk_fsno = lk_fsno.Substring(0,lk_fsno.Length-1);			
				lk_fsco = "(" + lk_fsco + ")";
				lk_fsno = "(" + lk_fsno + ")";
			}

			sql = "";
			sql = " select tk.id,tk.tinhchat,tk.stk,tk.ttk,sum(sdno" + mm1 + " + sdco" + mm1 + ") as sddk, ";
			sql += "sum("+(fsno.Length != 0? fsno +") as fsno," : "0 as fsno , ");
			sql += "sum("+(fsco.Length != 0? fsco +") as fsco," : "0 as fsco , ");
			sql += "sum("+(lk_fsno.Length != 0?lk_fsno +") as lkfsno," : "0 as lkfsno, ");
			sql += "sum("+(lk_fsco.Length != 0?lk_fsco +") as lkfsco," : "0 as lkfsco, ");
			sql = cutString(sql);
			sql += " from " + a.user + ".a_sd" + yy + " sd inner join " + a.user + ".a_dmtk tk on sd.stka= tk.id";
			sql += " where tk.stk like '" + stk + "%' and tk.c = 1 group by tk.id,tk.tinhchat,tk.stk,tk.ttk";

			try
			{
				ds_Data = a.get_data(sql);
				if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
				ds_Data.WriteXml("..\\xml\\KT_TongHopTaiKhoanTienGui.xml",XmlWriteMode.WriteSchema);
			}
			catch 
			{
				flag_dsnull=true;
			}		

			if (flag_dsnull) return null;
			else return ds_Data;


		}

		
		/// <summary>
		/// 29-05-2009
		/// </summary>
		public DataSet get_SoTongHopChiPhi(AccessData a, string stk, string idkm,string mm1,string mm2, string yy)
		{
			sql = "";
			sql = "select km.ma as ma, km.ten as ten, sum(ctct.stien) as sotien, ctct.kmno as kmno,tk.stk, tk.ttk ";
			sql += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tk ";
			sql += " on tk.id = ctct.tknoa inner join "+ a.user + ".a_dmkhoanmuc km on km.id = ctct.kmno ";
			sql += " where ctct.kmno >0 and tk.stk like '"+ stk + "%' and ctct.tonghop=1 ";
			sql += " and ct.thanggs between '" + mm1 + "' and '" + mm2 + "'";
			sql += (idkm != "" ? " and ctct.kmno= " + idkm + "" : "");
			sql += " group by km.ma,km.ten,ctct.kmno,tk.stk,tk.ttk";
			
			try
			{
				ds_Data = a.get_data(sql);
				if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
				ds_Data.WriteXml("..\\xml\\KT_SoTongHopChiPhi.xml",XmlWriteMode.WriteSchema);
			}
			catch 
			{
				flag_dsnull=true;
			}		

			if (flag_dsnull) return null;
			else return ds_Data;
		}

		public DataSet get_SoChiTietChiPhi(AccessData a, string stk, string idkm,string mm1,string mm2, string yy)
		{
			sql = "";
			sql = "select to_char(ct.ngayg,'dd/mm/yyyy') as ngayct, ct.soctg as soctg,km.ma as ma, km.ten as ten, ctct.noidung as noidung, ctct.stien as sotien,tk.stk, tk.ttk ";
			sql += " from " + a.user + ".a_ctct" + yy + " ctct inner join " + a.user + ".a_ct" + yy + " ct on ct.id = ctct.idct inner join " + a.user + ".a_dmtk tk ";
			sql += " on tk.id = ctct.tknoa inner join "+ a.user + ".a_dmkhoanmuc km on km.id = ctct.kmno ";
			sql += " where ctct.kmno >0 and tk.stk like '"+ stk + "%' and ctct.tonghop=1 ";
			sql += " and ct.thanggs between '" + mm1 + "' and '" + mm2 + "'";
			sql += (idkm != "" ? " and ctct.kmno= " + idkm + "" : "");
			try
			{
				ds_Data = a.get_data(sql);
				if ((ds_Data==null || ds_Data.Tables[0].Rows.Count<=0)) flag_dsnull=true;
				ds_Data.WriteXml("..\\xml\\KT_SoChiTietChiPhi.xml",XmlWriteMode.WriteSchema);
			}
			catch 
			{
				flag_dsnull=true;
			}		

			if (flag_dsnull) return null;
			else return ds_Data;
		}

		private string cutString(string sql)
		{
			StringBuilder strBuilder = new StringBuilder();
			strBuilder.Append(sql);
			sql = strBuilder.ToString();
			int last = sql.LastIndexOf(",");
			sql = sql.Substring(0,last);
			return sql;
		}
		#endregion 
				
		
	}
	  
}


